﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Xml;
using DataAccess;
using DataAccess.Entity;

namespace SkynetOLEDBAccess
{
    public class OledbDataAccess : AbstractDataAccess
    {
        #region Constructor
        /// <summary>
        /// 默认构造函数
        /// </summary>
        public OledbDataAccess()
        {
            
        }
      
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public OledbDataAccess(string connectionString)
        {
            this._mDbConnection = new OleDbConnection(connectionString);
        }

        #endregion

        #region IDataAccess 成员

        #region Support Property & method
        /// <summary>
        /// 返回数据库类型枚举成员(DatabaseType)
        /// </summary>
        public override DatabaseType DatabaseType
        {
            get { return DatabaseType.OleDBSupported; }
        }

        private readonly OleDbConnection _mDbConnection;
        /// <summary>
        /// 返回数据库连接接口
        /// </summary>
        public override IDbConnection DbConnection
        {
            get
            {
                return _mDbConnection;
            }
        }

        private OleDbTransaction _trans;
        /// <summary>
        /// 开始一个事务
        /// </summary>
        /// <returns>数据库事务接口</returns>
        public override IDbTransaction BeginTransaction()
        {
            _trans = _mDbConnection.BeginTransaction();
            return _trans;
        }

        #endregion Support Property & method

        #region ExecuteNonQuery
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="row">DataRow对象</param>
        /// <param name="sqlStruct">SqlStruct对象,封装Sql语句及参数</param>
        /// <returns>执行SQL语句影响的行数</returns>
        public override int ExecuteNonQuery(CommandType commandType, string commandText, QueryParameterCollection commandParameters, DataRow row, SqlStruct sqlStruct)
        {
            int tmpValue = 0;
            base.LogOutPut(new Func<int>(() =>
            {


                OleDbCommand cmd = new OleDbCommand();
                if (sqlStruct == null)
                    PrepareCommand(cmd, commandType, commandText, commandParameters);
                else
                {
                    for (int i = 0; i < sqlStruct.ParamFields.Length; i++)
                    {
                        if (sqlStruct.ParamFields[i].ParamName.IndexOf('_') != 1)
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName]);
                        else
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
                    }
                    PrepareCommand(cmd, CommandType.Text, sqlStruct.SqlString, commandParameters);
                }
                tmpValue = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return tmpValue;
            }), "ExecuteNonQuery", commandText, commandParameters);

            return tmpValue;
        }
        #endregion ExecuteNonQuery

        #region ExecuteDataSet
        /// <summary>
        /// 执行SQL语句,返回DataSet对象.
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="ds">DataSet对象</param>
        /// <param name="tableName">DataSet中表的名称</param>
        /// <returns>DataSet对象</returns>
        public override DataSet ExecuteDataset(CommandType commandType, string commandText, QueryParameterCollection commandParameters, DataSet ds, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
            {

                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                if (Object.Equals(tableName, null) || (tableName.Length < 1))
                    da.Fill(ds);
                else
                    da.Fill(ds, tableName);
                //			System.Windows.Forms.MessageBox.Show(tableName);

                cmd.Parameters.Clear();
                //Console.WriteLine(ds.Tables.Count.ToString());
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0].Rows.Count;
                }
                else
                {
                    return 0;
                }
            }), "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        public override DataSet ExecuteDataset(string commandText, QueryParameterCollection commandParameters, DataSet ds, int startRecord, int maxRecords, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
            {

                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, CommandType.Text, commandText, commandParameters);

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                if (Object.Equals(tableName, null) || (tableName.Trim() == string.Empty))
                    da.Fill(ds);
                else
                    da.Fill(ds, startRecord, maxRecords, tableName);
                cmd.Parameters.Clear();
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0].Rows.Count;
                }
                else
                {
                    return 0;
                }
            }), "ExecuteDataset", commandText, commandParameters);

            return ds;
        }

        #endregion ExecuteDataSet

        #region ExecuteReader
        /// <summary>
        /// 执行SQL语句,返回IDataReader接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>IDataReader</returns>
        public override IDataReader ExecuteReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            IDataReader dr = null;
            base.LogOutPut(new Func<int>(() =>
            {

                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteReader", commandText, commandParameters);
            return dr;
        }
        #endregion ExecuteReader

        #region ExecuteScalar
        /// <summary>
        /// 执行SQL语句,返回object接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>object</returns>
        public override object ExecuteScalar(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            object tmpValue = null;
            base.LogOutPut(new Func<int>(() =>
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                tmpValue = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteScalar", commandText, commandParameters);
            return tmpValue;
        }
        #endregion ExecuteScalar

        #region ExecuteXmlReader
        /// <summary>
        /// 执行SQL语句,返回XmlReader对象
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>XmlReader</returns>
        public override XmlReader ExecuteXmlReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            //OleDbCommand cmd = new OleDbCommand();
            //PrepareCommand(cmd, commandType, commandText, commandParameters);
            //XmlReader reader = cmd.ExecuteXmlReader();
            //cmd.Parameters.Clear();
            return null;
        }

        #endregion ExecuteXmlReader

        #endregion
        /// <summary>
        /// 处理OleDbCommand对象,设置参数等信息.
        /// </summary>
        /// <param name="cmd">OleDbCommand对象</param>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        private void PrepareCommand(OleDbCommand cmd, CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            commandText = commandText.Replace(":", "@");
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = this._mDbConnection;
            cmd.Transaction = _trans;
            if ((commandParameters != null) && (commandParameters.Count > 0))
            {
                for (int i = 0; i < commandParameters.Count; i++)
                {
                    cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value);
                    cmd.Parameters[i].Direction = commandParameters[i].Direction;
                }
            }
        }




        #region IDataStructure

        public override void FillingTables(List<TableInfo> info)
        {
            DataTable table = this._mDbConnection.GetSchema("Tables");
            info.AddRange(from DataRow row in table.Rows
                          select new TableInfo
                                     {
                                         TableName = row["TABLE_NAME"].ToString()
                                     });
        }

        public override void FillingColumns(List<ColumnInfo> info)
        {
            DataTable columns = this._mDbConnection.GetSchema("Columns");
            DataTable types = this._mDbConnection.GetSchema("DataTypes");
            foreach(DataRow row in columns.Rows)
            {
                string typeName;
                string length;
                if(string.IsNullOrEmpty(row["CHARACTER_MAXIMUM_LENGTH"].ToString()))
                {
                    DataRow[] drs = types.Select(string.Format("NativeDataType = {0} and ColumnSize = {1}",
                                 row["DATA_TYPE"],
                                 row["NUMERIC_PRECISION"]));
                    typeName = drs[0]["TypeName"].ToString();
                    length = row["NUMERIC_PRECISION"].ToString();
                }
                else
                {
                     DataRow[] drs = types.Select(string.Format("NativeDataType = {0} and ColumnSize = {1}",
                                 row["DATA_TYPE"],
                                 row["CHARACTER_MAXIMUM_LENGTH"]));
                    typeName = drs[0]["TypeName"].ToString();
                    length = row["CHARACTER_MAXIMUM_LENGTH"].ToString();
                }
                info.Add(new ColumnInfo
                                     {
                                         TableName=row["TABLE_NAME"].ToString(),
                                         ColumnName = row["COLUMN_NAME"].ToString(),
                                         ColumnIndex = Convert.ToInt32(row["ORDINAL_POSITION"].ToString()),
                                         IsNull = row["IS_NULLABLE"].ToString().ToUpper() == "TRUE" ,
                                         TypeName = typeName,
                                         Length = length,
                                         DataScale = row["NUMERIC_SCALE"].ToString()
                                     });
            }
          
        }

        public override void FillingIndexs(List<IndexInfo> info)
        {
            
        }

        public override void FillingRelations(List<RelationshipInfo> info)
        {
        }

        public override void FillingViews(List<ViewInfo> info)
        {

        }

        public override void FillingProcs(List<ProcInfo> info)
        {
        }

        public override void FillingProcParams(List<ProcParamInfo> info)
        {
        }

        public override void FillingTypeInfos(List<TypeNameInfos> info)
        {
            DataTable table = this._mDbConnection.GetSchema("DataTypes");
            if (table != null && table.Rows.Count > 0)
            {
                info.AddRange(from DataRow row in table.Rows
                              select new TypeNameInfos
                              {
                                  TypeName = row["TypeName"].ToString()
                              });
            }
        }
        #endregion

    }
}
